MySQLでテーブル結合

#初心者向け

#データベース

takahara

2015.12.24

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

SQLのテーブル結合の理解を深める為に調査した結果を記事にしました。
想定した状況に必要な情報を複数のテーブルから導き出すという設定です。

想定した状況１

目的

「店舗ごとの在庫の金額と数量を調べる必要が出た。」

テーブル

テーブル１：shop

店舗の商品ごとの在庫です。
id = 行番号、shop_name = 店舗名、item_id = 商品ID、quantity = 数量

+----+-------------+---------+----------+
| id | shop_name   | item_id | quantity |
+----+-------------+---------+----------+
|  1 | Music Store |       1 |     1000 |
|  2 | Music Store |       2 |      200 |
|  3 | Music Store |       4 |      100 |
|  4 | Book Store  |       3 |     1000 |
|  5 | Book Store  |       4 |      500 |
+----+-------------+---------+----------+

テーブル２：item

商品情報です。
item_id = 商品ID、item = 商品名、price = 単価、category = 商品の種別

+---------+-----------+-------+----------+
| item_id | item_name | price | category |
+---------+-----------+-------+----------+
|       1 | cd        |  1500 | music    |
|       2 | record    |  2000 | music    |
|       3 | novel     |   800 | book     |
|       4 | magazine  |   500 | book     |
|       5 | dvd       |  4000 | movie    |
+---------+-----------+-------+----------+

ポイント

２つのテーブル”shop"と"item"は、"item_id"で結びついています。

方法１：INNER JOIN（内部結合）

構文

SELECT
  テーブル.表示するフィールド
FROM
  テーブル１ INNER JOIN テーブル２
ON
  テーブル.比較するフィールド

実行するSQL文

SELECT
  shop.shop_name,
  shop.item_id, 
  item.item_name, 
  item.price, 
  shop.quantity 
FROM
  shop INNER JOIN item 
ON
  shop.item_id = item.item_id;

実行結果

+-------------+---------+-----------+-------+----------+
| shop_name   | item_id | item_name | price | quantity |
+-------------+---------+-----------+-------+----------+
| Music Store |       1 | cd        |  1500 |     1000 |
| Music Store |       2 | record    |  2000 |      200 |
| Book Store  |       3 | novel     |   800 |     1000 |
| Music Store |       4 | magazine  |   500 |      100 |
| Book Store  |       4 | magazine  |   500 |      500 |
+-------------+---------+-----------+-------+----------+

解説

SQL文の意味は、
「 shopテーブルのitem_idに一致する項目を、itemテーブルのitem_idと比較して抜き出す。
　表示するのはSELECTで指定したフィールドのみ。」
となります。

方法２：OUTER JOIN（外部結合）

OUTER JOINの場合は、FROM句で元となるテーブルをRIGHTとLEFTで指定できます。
OUTER JOINを中心として左か右という意味になっています。

RIGHTの場合

構文

SELECT
  テーブル.表示するフィールド
FROM
  照合するテーブル RIGHT OUTER JOIN 元となるテーブル
ON
  テーブル.比較するフィールド

実行するSQL文

SELECT
  shop.shop_name,
  shop.item_id, 
  item.item_name, 
  item.price, 
  shop.quantity 
FROM
  shop RIGHT OUTER JOIN item 
ON
  shop.item_id = item.item_id;

実行結果

+-------------+---------+-----------+-------+----------+
| shop_name   | item_id | item_name | price | quantity |
+-------------+---------+-----------+-------+----------+
| Music Store |       1 | cd        |  1500 |     1000 |
| Music Store |       2 | record    |  2000 |      200 |
| Music Store |       4 | magazine  |   500 |      100 |
| Book Store  |       3 | novel     |   800 |     1000 |
| Book Store  |       4 | magazine  |   500 |      500 |
| NULL        |    NULL | dvd       |  4000 |     NULL |
+-------------+---------+-----------+-------+----------+

解説

INNER JOINの時より１行増えています。
これは、元となったテーブルがitemなので、比較対象のitem側のitem_idの項目が全て表示されるためです。
shop_nameとitem_idとquantityの値がNULLなのは、item.item_idの"dvd"に該当する項目がshopには無いためです。

LEFTの場合

構文

SELECT
  テーブル.表示するフィールド
FROM
  元となるテーブル LEFT OUTER JOIN 照合するテーブル 
ON
  テーブル.比較するフィールド

SQL文

SELECT
  shop.shop_name,
  shop.item_id, 
  item.item_name, 
  item.price, 
  shop.quantity 
FROM
  shop LEFT OUTER JOIN item 
ON
  shop.item_id = item.item_id;

実行結果

+-------------+---------+-----------+-------+----------+
| shop_name   | item_id | item_name | price | quantity |
+-------------+---------+-----------+-------+----------+
| Music Store |       1 | cd        |  1500 |     1000 |
| Music Store |       2 | record    |  2000 |      200 |
| Book Store  |       3 | novel     |   800 |     1000 |
| Music Store |       4 | magazine  |   500 |      100 |
| Book Store  |       4 | magazine  |   500 |      500 |
+-------------+---------+-----------+-------+----------+

解説

RIGHTの場合と違い、shopを元にしているので、itemの該当しない項目は表示されず、NULLは有りません。

想定した状況２

目的

「状況１結果に、追加テーブルcategoryの情報も必要になった。
　ただし今度は店名'Music Store'のみが欲しい。」

追加テーブル

テーブル３：category

テーブル２のcategoryと結びついています。

+----------+--------+
| category | data   |
+----------+--------+
| music    | ongaku |
| book     | hon    |
+----------+--------+

INNER JOINで結合

実行するSQL文

一部、書き方を変えてSQL文を短縮しています。
・INNER JOINはJOINで判別してくれます。もしOUTER JOINにしたい場合はLEFTかRIGHTを付けます。
・ASでテーブル名の略称を作っています。shop=S、item=I、category=C。

SELECT
  S.shop_name,
  S.item_id, 
  I.item_name,
  I.price, 
  S.quantity,
  I.category, 
  C.data 
FROM
  shop AS S 
  JOIN item AS I
  ON S.item_id = I.item_id
    JOIN category AS C
    ON I.category = C.category
WHERE
  shop_name='Music Store';

実行結果

+-------------+---------+-----------+-------+----------+----------+--------+
| shop_name   | item_id | item_name | price | quantity | category | data   |
+-------------+---------+-----------+-------+----------+----------+--------+
| Music Store |       1 | cd        |  1500 |     1000 | music    | ongaku |
| Music Store |       2 | record    |  2000 |      200 | music    | ongaku |
| Music Store |       4 | magazine  |   500 |      100 | book     | hon    |
+-------------+---------+-----------+-------+----------+----------+--------+